CREATE DATABASE QuanLyKhachSan
Go

USE QuanLyKhachSan
Go

CREATE TABLE THAMSO
(
	MaTS int not null,
	TenTS nvarchar(30),
	GiaTriTS nvarchar(30)
)
Go

ALTER TABLE THAMSO ADD CONSTRAINT pk_ThamSo primary key (MaTS)
GO

CREATE TABLE LOAIPHONG
(
	ID_LP int not null,
	MaLP char(10) not null,
	TenLP nvarchar(30),
	DonGia money,
	MoTa ntext
)
Go

ALTER TABLE LOAIPHONG ADD CONSTRAINT pk_LoaiPhong primary key (ID_LP)
Go

ALTER TABLE LOAIPHONG ADD CONSTRAINT u_LoaiPhong unique (MaLP)
Go

CREATE TABLE LOAITINHTRANG
(
	MaTinhTrang int not null,
	TenTinhTrang nvarchar(30)
)
Go

ALTER TABLE LOAITINHTRANG ADD CONSTRAINT pk_LoaiTinhTrang primary key(MaTinhTrang)
Go

CREATE TABLE PHONG
(
	ID_Phong int not null,
	MaP char(10) not null,
	TenP nvarchar(20),
	MaLoai int,
	GhiChu ntext,
	MaTinhTrang int
)
Go

ALTER TABLE PHONG ADD CONSTRAINT pk_Phong primary key (ID_Phong)
Go

ALTER TABLE PHONG ADD CONSTRAINT u_Phong unique(MaP)
Go

ALTER TABLE PHONG ADD CONSTRAINT fk_Phong_TinhTrang foreign key(MaTinhTrang) references LOAITINHTRANG(MaTinhTrang)
Go

ALTER TABLE PHONG ADD CONSTRAINT fk_Phong_LoaiPhong foreign key(MaLoai) references LOAIPHONG(ID_LP)
Go

CREATE TABLE KHACHHANG
(
	MaKH int not null,
	TenKH nvarchar(30),
	DienThoai char(15),
	DiaChi nvarchar(50)
)
Go

ALTER TABLE KHACHHANG ADD CONSTRAINT pk_KhachHang primary key(MaKH)
Go

CREATE TABLE LOAIKHACH
(
	ID_LK int not null,
	MaLK char(10) not null,
	TenLK nvarchar(30),
	HeSo float
)
Go

ALTER TABLE LOAIKHACH ADD CONSTRAINT pk_LoaiKhach primary key(ID_LK)
Go

ALTER TABLE LOAIKHACH ADD CONSTRAINT u_LoaiKhach unique(MaLK)
Go

CREATE TABLE KHACHTHUEPHONG
(
	MaKT int not null,
	HoTen nvarchar(30),
	CMND_PASSPORT char(13),
	DienThoai char(15),
	DiaChi nvarchar(50),
	MaLoaiKhach int
)
Go

ALTER TABLE KHACHTHUEPHONG ADD CONSTRAINT pk_KhachThuePhong primary key(MaKT)
Go

ALTER TABLE KHACHTHUEPHONG ADD CONSTRAINT fk_KhachThuePhong_LoaiKhach foreign key(MaLoaiKhach) references LOAIKHACH(ID_LK)
Go

CREATE TABLE PHIEUTHUEPHONG
(
	MaPT int not null,
	MaKH int not null,
	MaP int,
	DonGia money,
	NgayLapPhieu datetime,
	NgayBatDau datetime,
	TienCoc money,
	TinhTrang int
)
Go

ALTER TABLE PHIEUTHUEPHONG ADD CONSTRAINT c_PhieuThuePhong check (NgayLapPhieu <= NgayBatDau)
Go

ALTER TABLE PHIEUTHUEPHONG ADD CONSTRAINT pk_PhieuThuePhong primary key (MaPT)
Go

ALTER TABLE PHIEUTHUEPHONG ADD CONSTRAINT fk_PhieuThuePhong_KhachHang foreign key(MaKH) references KHACHHANG(MaKH)
Go

ALTER TABLE PHIEUTHUEPHONG ADD CONSTRAINT fk_PhieuThuePhong_Phong foreign key(MaP) references PHONG(ID_Phong)
Go

CREATE TABLE CT_PHIEUTHUEPHONG
(
	MaCTPT int not null,
	MaPT int null,
	MaKT int
)
Go

ALTER TABLE CT_PHIEUTHUEPHONG ADD CONSTRAINT pk_ChiTietPhieuThue primary key(MaCTPT)
Go

ALTER TABLE CT_PHIEUTHUEPHONG ADD CONSTRAINT fk_ChiTietPhieuThue_PhieuThuePhong foreign key(MaPT) references PHIEUTHUEPHONG(MaPT)
Go

ALTER TABLE CT_PHIEUTHUEPHONG ADD CONSTRAINT fk_ChiTietPhieuThue_KhachThue foreign key(MaKT) references KHACHTHUEPHONG(MaKT)
Go

CREATE TABLE HOADON
(
	MaHD int not null,
	MaKH int not null,
	NgayThanhToan datetime,
	TongTien money
)
Go

ALTER TABLE HOADON ADD CONSTRAINT pk_HoaDon primary key(MaHD)
Go

ALTER TABLE HOADON ADD CONSTRAINT fk_HoaDon_KhachHang foreign key(MaKH) references KhachHang(MaKH)
Go

CREATE TABLE CT_HOADON
(
	MaCTHD int not null,
	MaHD int not null,
	MaP int not null,
	DonGia money,
	SoNgayThue int,
	ThanhTien money
)
Go

ALTER TABLE CT_HOADON ADD CONSTRAINT pk_CTHoaDon primary key(MaCTHD)
Go

ALTER TABLE CT_HOADON ADD CONSTRAINT fk_CTHoaDon_HoaDon foreign key(MaHD) references HOADON(MaHD)
Go

ALTER TABLE CT_HOADON ADD CONSTRAINT fk_CTHoaDon_PhieuThuePhong foreign key(MaP) references PHONG(ID_Phong)
Go

CREATE TABLE NGUOIDUNG
(
	MaUser int not null,
	Username char(15),
	Password char(15),
	Quyen int not null
)
Go

ALTER TABLE NGUOIDUNG ADD CONSTRAINT pk_NguoiDung primary key(MaUser)
Go

CREATE TABLE THONGTINUSER
(
	MaUser int not null,
	HoTen nvarchar(50),
	CMND char(13),
	DienThoai char(15),
	Email char(30),
	DiaChi nvarchar(50)
)
Go

ALTER TABLE THONGTINUSER ADD CONSTRAINT pk_ThongTinUser primary key(MaUser)
Go

ALTER TABLE THONGTINUSER ADD CONSTRAINT fk_ThongTinUser_NguoiDung foreign key(MaUser) references NGUOIDUNG(MaUser)
Go